A database is a collection of information. A relational database is a database that is perceived by its users as a collection of tables. Each table arranges items and attributes of the items in rows and columns respectively. Each table row corresponds to an item (also referred to as a record or tuple), and each table column corresponds to an attribute of the item (referred to as a field, an attribute type, or field type). To retrieve information from a database, the user of a database system constructs a query. A query contains one or more operations that specify information to retrieve, manipulate, or update from the database. The system scans tables in the database and processes the information retrieved from the tables to execute the query.
Queries of databases represent one form of a transaction that may be performed on a database or other form of computer system. For example, data updates or other data maintenance may also be performed. In complex data processing systems, queries or other transactions may execute in parallel or be programmed to execute concurrently. Additionally, there may be multiple types of transactions that may be executed at a time. A multi-programming level (MPL) is a number of transactions that are scheduled to be executed concurrently. Accordingly, finding a good MPL for each type of transaction may be difficult. If an MPL is too low, then response time and throughput may suffer. If an MPL is too high, then there may be excessive resource contention and response time and throughput may again suffer.
On the one hand, it may be desirable to use system resources efficiently so that service objectives are met (as opposed to missing service objectives despite system resources remaining unused). On the other hand, it may be important to avoid system overload, which occurs when a database system performs inefficiently because too many queries are being processed, or the workload is otherwise too heavy for the data processing system to efficiently process.
As more and more queries are processed on a database system, the number of queries the system processes per minute, as a measure of throughput, may increase at first, as resources become more fully utilized. However, once the database system is overloaded, attempting to process more queries may cause throughput to decrease. This means that the database system completes fewer queries per minute in an overload state than in a non-overload state. It is noted that in an overload state of the database system, not all the resources used by the database system may actually be overloaded. For example, the hard-disk-drive resource of a database system may be overloaded, but queries that do not result in access of this resource may still be added without inordinately impacting the throughput of the database system as a whole, as is the case if queries that result in access of this resource are added. It is also possible in some database systems that the queries that do not result in access of the hard disk drive will still suffer from impaired throughput because they may be forced to wait for other queries that do require access to the hard drive resource.
It also may be difficult to predict how the performance of a given type of query whose behavior is well-known under favorable runtime conditions will degrade under resource contention. The difficulty may be compounded when multiple types of queries run at the same time. Many different types of queries can be run on a data warehouse, ranging from short-running queries used to enable online transactions (OLTP queries), to longer-running queries used to generate reports, and to very long-running transactions used to perform complicated analysis or database maintenance.